/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.com.pps.dao;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import vn.com.pps.entities.CreditMemo;

/**
 *
 * @author Dai
 */
public class CourseCreditMemoDAO extends BaseDAO implements ICourseCreditMemoDAO {

    private Logger logger = Logger.getLogger(CourseCreditMemoDAO.class);

    @Override
    public int insert(CreditMemo creditMemo) {
        String sql = "INSERT INTO tblcreditmemo(ID,CreditType,CourseID,Amount,Reason,CreatedOn,LastModified) VALUES(?,?,?,?,?,?,?)";
        Object[] params = new Object[]{null, creditMemo.getCreditType(),
            creditMemo.getCourseID(), creditMemo.getAmount(), creditMemo.getReason(),
            creditMemo.getCreatedDate(), creditMemo.getLastModifiedDate()
        };

        logger.info("Reason:" + creditMemo.getReason());
        PreparedStatementCreatorFactory psc
                = new PreparedStatementCreatorFactory(sql);
        psc.addParameter(new SqlParameter("ID", Types.INTEGER));
        psc.addParameter(new SqlParameter("CreditType", Types.VARCHAR));
        psc.addParameter(new SqlParameter("CourseID", Types.INTEGER));
        psc.addParameter(new SqlParameter("Amount", Types.DECIMAL));
        psc.addParameter(new SqlParameter("Reason", Types.VARCHAR));
        psc.addParameter(new SqlParameter("CreatedOn", Types.TIMESTAMP));
        psc.addParameter(new SqlParameter("LastModified", Types.TIMESTAMP));
        psc.setReturnGeneratedKeys(true);
        KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(psc.newPreparedStatementCreator(params),
                generatedKeyHolder);
        return generatedKeyHolder.getKey().intValue();
    }

    @Override
    public int update(CreditMemo creditMemo) {
        System.out.println("@updateCreditMemo");
        try {
            String sqlUpdate = "UPDATE tblcreditmemo SET CreditType=?, Amount=?,Reason=?,LastModified=? WHERE ID=?";
            Object[] params = new Object[]{
                creditMemo.getCreditType(), creditMemo.getAmount(),
                creditMemo.getReason(), creditMemo.getLastModifiedDate(),
                creditMemo.getId()
            };
            System.out.println("Type:" + creditMemo.getCreditType());
            System.out.println("Amount:" + creditMemo.getAmount());
            System.out.println("Note:" + creditMemo.getReason());
            System.out.println("TimeStamp::" + creditMemo.getLastModifiedDate());
            System.out.println("ID::" + creditMemo.getId());
            PreparedStatementCreatorFactory psc
                    = new PreparedStatementCreatorFactory(sqlUpdate);
            psc.addParameter(new SqlParameter("CreditType", Types.VARCHAR));
            psc.addParameter(new SqlParameter("Amount", Types.DECIMAL));
            psc.addParameter(new SqlParameter("Reason", Types.VARCHAR));
            psc.addParameter(new SqlParameter("LastModified", Types.TIMESTAMP));
            psc.addParameter(new SqlParameter("ID", Types.INTEGER));
            getJdbcTemplate().update(psc.newPreparedStatementCreator(params));
            System.out.println("Finish update");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return creditMemo.getId();
    }

    @Override
    public int delete(int id) {
        String sqlDelete = "DELETE FROM tblcreditmemo WHERE ID=" + id;
        getJdbcTemplate().execute(sqlDelete);
        return 0;
    }

    @Override
    public Page<CreditMemo> getCreditMemos(int pageNo, int pageSize, Integer courseID) {
        // String sqlQuery = "SELECT * FROM TblCourses WHERE " + whereClause;
        String sqlQuery = "SELECT * FROM tblcreditmemo WHERE CourseID=" + courseID;
        String sqlQueryCount = "SELECT COUNT(*) FROM tblcreditmemo WHERE CourseID =" + courseID;
        PaginationHelper<CreditMemo> ph = new PaginationHelper<CreditMemo>();
        return ph.fetchPage(
                this.getJdbcTemplate(),
                sqlQueryCount,
                sqlQuery,
                null,
                pageNo,
                pageSize, new ParameterizedRowMapper<CreditMemo>() {
                    @Override
                    public CreditMemo mapRow(ResultSet rs, int i) throws SQLException {
                        CreditMemo creditMemo = new CreditMemo();
                        creditMemo.setId(rs.getInt("ID"));
                        creditMemo.setCourseID(rs.getInt("CourseID"));
                        creditMemo.setCreditType(rs.getString("CreditType"));
                        creditMemo.setAmount(rs.getBigDecimal("Amount"));
                        creditMemo.setReason(rs.getString("Reason"));
                        creditMemo.setCreatedDate(rs.getTimestamp("CreatedOn"));
                        creditMemo.setLastModifiedDate(rs.getTimestamp("LastModified"));
                        return creditMemo;
                    }
                });
    }

    @Override
    public int getCreditMemoCountByCourseID(Integer id) {
        String sqlQueryCount = "SELECT COUNT(*) FROM tblcreditmemo WHERE CourseID =" + id;
        return getJdbcTemplate().queryForInt(sqlQueryCount);
    }

    @Override
    public CreditMemo getCreditMemo(Integer id) {
        String sqlQuery = "SELECT tblcreditmemo.id,tblcreditmemo.CreditType,\n"
                + "tblcreditmemo.CourseID,tblcreditmemo.Amount,\n"
                + "tblcreditmemo.Reason,tblcreditmemo.CreatedOn,\n"
                + "tblcreditmemo.LastModified,tblcourses.CourseName\n"
                + "FROM tblcreditmemo,tblcourses\n"
                + "WHERE tblcreditmemo.CourseID=tblcourses.ID AND tblcreditmemo.id=" + id;
        return (CreditMemo) getJdbcTemplate().queryForObject(sqlQuery, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                CreditMemo creditMemo = new CreditMemo();
                creditMemo.setId(rs.getInt("ID"));
                creditMemo.setCourseID(rs.getInt("CourseID"));
                creditMemo.setCreditType(rs.getString("CreditType"));
                creditMemo.setAmount(rs.getBigDecimal("Amount"));
                creditMemo.setReason(rs.getString("Reason"));
                creditMemo.setCreatedDate(rs.getTimestamp("CreatedOn"));
                creditMemo.setLastModifiedDate(rs.getTimestamp("LastModified"));
                return creditMemo;
            }
        });
    }

    @Override
    public List<CreditMemo> getCreditMemosByCourse(int courseID) {
        String sqlQuery = "SELECT * FROM tblcreditmemo WHERE CourseID=" + courseID;
        return getJdbcTemplate().query(sqlQuery, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                CreditMemo creditMemo = new CreditMemo();
                creditMemo.setId(rs.getInt("ID"));
                creditMemo.setCourseID(rs.getInt("CourseID"));
                creditMemo.setCreditType(rs.getString("CreditType"));
                creditMemo.setAmount(rs.getBigDecimal("Amount"));
                creditMemo.setReason(rs.getString("Reason"));
                creditMemo.setCreatedDate(rs.getTimestamp("CreatedOn"));
                creditMemo.setLastModifiedDate(rs.getTimestamp("LastModified"));
                return creditMemo;
            }
        });

    }

}
